<?php
require('config.php');


$DBConnection = new DBConnection();

/**
 * Returns a list of matching locations to the entered string
 * @param string $loc_string
 * @return array
 */
function getLocation($loc_string){
	global $DBConnection;
	
	$loc_string = $loc_string . "%";
	$sql = "SELECT id, name FROM locations WHERE name LIKE :loc AND loc_type = 3";
	$resultset = $DBConnection->query($sql, array(':loc' => $loc_string));
	
	if($resultset) {
		return $resultset->fetchAll();
	}
	else {
		return false;
	}
}


/**
 * Returns a list of locations for populating the drop down list on the home page
 * @return array
 */
function getLocationsForDropDown(){
	global $DBConnection;
	
	$sql = "SELECT id, name FROM locations WHERE loc_type = 3";
	$resultset = $DBConnection->query($sql, null);
	
	if($resultset) {
		return $resultset->fetchAll();
	}
	else {
		return false;
	}
}

/**
 * Returns a list of all packages for populating drop down list on the home page
 * @return boolean
 */
function getAllPackagesForDropDown(){
	global $DBConnection;
	
	$sql = "SELECT id, name FROM packages";
	$resultset = $DBConnection->query($sql, null);
	
	if($resultset) {
		return $resultset->fetchAll();
	}
	else {
		return false;
	}
}


/**
 * Saves Speedtest data
 * @param int $downspeed
 * @param int $upspeeed
 * @param int $userid
 * @param int $locid
 * @param int $packid
 */
function saveSpeedTest($downspeed, $upspeed, $userid, $locid, $packid){
	global $DBConnection;
	
	$sql = "INSERT INTO tests 
    		(pkgid, locid, userid, down, up, time) 
   		    VALUES(:packid, :locid, :userid, :downspeed, :upspeed, now())";
	
	$params = array(":packid" => $packid,
					":locid" => $locid,
					":userid" => $userid,
					":downspeed" => $downspeed,
					":upspeed" => $upspeed);
	
	$ret = $DBConnection->query($sql, $params);

    return $ret;
}

/**
 * Gets Location name by ID
 * @param int $locid
 * @return string
 */
function getLocationNameById($locid){
	global $DBConnection;
	
	$sql = "SELECT name FROM locations WHERE id=:id";
	$resultset = $DBConnection->query($sql, array(":id" => $locid));
	
	if($resultset) {
		$row = $resultset->fetch(PDO::FETCH_ASSOC);
		return $row['name'];
	}
	else {
		return false;
	}
}

/**
 * Get speed data for package and location
 * @param unknown_type $pkgid
 * @param unknown_type $locid
 * @return boolean
 */
function getSpeedsByPackageAndLocation($pkgid, $locid){
	global $DBConnection;
	
	$sql = "SELECT down, up, time FROM tests WHERE pkgid=:pkgid AND locid=:locid ORDER BY time LIMIT 0,10";
	$resultset = $DBConnection->query($sql, array(":pkgid" => $pkgid, ":locid" => $locid));
	
	if($resultset) {
		return $resultset->fetchAll(PDO::FETCH_ASSOC);
	}
	else {
		return false;
	}
}

/**
 * Get speed data for package and location
 * @param unknown_type $pkgid
 * @param unknown_type $locid
 * @return boolean
 */
function getAllResults(){
	global $DBConnection;
	
	$sql = "SELECT down, up, time FROM tests ORDER BY time LIMIT 5";
	$resultset = $DBConnection->query($sql, array());
	
	if($resultset) {
		return $resultset->fetchAll(PDO::FETCH_ASSOC);
	}
	else {
		return false;
	}
}

/**
 * Gets the package name by package id
 * @param int $pkgid
 * @return string
 */
function getPackageNameById($pkgid){
	global $DBConnection;
	
	$sql = "SELECT name FROM packages WHERE id=:id";
	$resultset = $DBConnection->query($sql, array(":id" => $pkgid));
	
	if($resultset) {
		$row = $resultset->fetch(PDO::FETCH_ASSOC);
		return $row['name'];
	}
	else {
		return false;
	}
}
